/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package db;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;
import models.Customer;
import models.Order;


/**
 *
 * @author Steffen
 */
public class CustomerDB extends AbstractDb implements CustomerInterface {

    public int getBiggestNo() {
        int biggest = 0;
        try {
            ResultSet rs = this.db.query("SELECT MAX(customer_id) AS max FROM Costumer");
            if (rs != null && rs.next()) {
                biggest = rs.getInt("max");
            }
        } catch (SQLException ex) {
            Logger.getLogger(CustomerDB.class.getName()).log(Level.SEVERE, null, ex);
        }
        return biggest;
    }

    public boolean addCustomer(Customer cus)
        {
        boolean succes = false;
        int r = this.db.queryUpdate("UPDATE Costumer SET "+
                "costumer_name ='"+ cus.getName()+"', "+
                "costumer_address ='"+ cus.getAddress()+"', "+
                "costumer_zipcode ='"+ cus.getZipcode()+"', "+
                "costumer_phoneNo ='"+ cus.getPhoneno()+"' " +
                "WHERE customer_id = " + cus.getId() + ";");

        if (r>0) {
            succes = true;
        }
        return succes;
    }

    public int updateCustomer(Customer cus)
	{
		Customer cusObj  = cus;
		int rc=-1;

		String query="UPDATE Customer SET "+
		 	  "costumer_name ='"+ cus.getName()+"', "+
                          "costumer_address ='"+ cus.getAddress()+"', "+
                          "costumer_zipcode ='"+ cus.getZipcode()+"', "+
                          "costumer_phoneNo ='"+ cus.getPhoneno()+"' " +
                          "WHERE customer_id = " + cus.getId() + ";";
                try{ // update customer
	 		rc = this.db.queryUpdate(query);
		}//slut try
	 	catch(Exception ex){
	 	 	System.out.println("Update exception in customer db: "+ex);
	  	}
		return(rc);
	}
        
    public Customer getCustomer(int cusid)
        {
        Customer obj = null;
        try {
            ResultSet rs = this.db.query(""
                    + "SELECT * "
                    + "FROM Customer "
                    + "JOIN Cities ON customer_zipcode = cities_zipcode "
                    + "WHERE customer_id = " + cusid);
            if (rs != null && rs.next()) {
                int id = rs.getInt("customer_id");
                String name = rs.getString("customer_name");
                String address = rs.getString("customer_address");
                int zipcode = rs.getInt("customer_zipcode");
                String city = rs.getString("cities_name");
                int phoneno = rs.getInt("customer_phoneNo");
                obj = new Customer(id, name, address, zipcode, city, phoneno);
            }
        } catch (SQLException ex) {
            Logger.getLogger(CustomerDB.class.getName()).log(Level.SEVERE, null, ex);
        }
        return obj;
    }

    public ArrayList<Customer> searchCustomers(int customerId, String customerName) {
        ArrayList<Customer> customers = new ArrayList<Customer>();
        if(customerId > 0 || customerName.length() > 0) {
            try {
                String queryAdd = "";
                
                if(customerId > 0) {
                    queryAdd = "customer_id = " + customerId;
                }
                
                if(customerName.length() > 0) {
                    // Adding a " AND " if there we added something before this
                    queryAdd += queryAdd.length() > 0 ? " OR " : "";
                    queryAdd += "customer_name LIKE '%" + customerName + "%'";
                }
                
                ResultSet items = this.db.query(""
                    + "SELECT * "
                    + "FROM Customer "
                    + "JOIN Cities ON customer_zipcode = cities_zipcode "
                    + "WHERE " + queryAdd);

                if (items != null) {
                    while(items.next()) {
                        Customer customer = new Customer(
                                items.getInt("customer_id"),
                                items.getString("customer_name"),
                                items.getString("customer_address"),
                                items.getInt("customer_zipcode"),
                                items.getString("cities_name"),
                                items.getLong("customer_phoneNo"));

                        customers.add(customer);
                    }
                }
            } catch (SQLException ex) {
                Logger.getLogger(OrderDb.class.getName()).log(Level.SEVERE, null, ex);
            }
        }

        return customers;
    }

    public int deleteCustomer(int id)
	{
               int rc=-1;

	  	String query="DELETE FROM customer "
                        + "WHERE id = '" + id + "'";
                System.out.println(query);
	  	try{ // delete from customer


	 	  	rc = this.db.queryUpdate(query);

  		}//slut try
   	        catch(Exception ex){
	 	  	System.out.println("Delete exception in customer db: "+ex);
   	        }
		return(rc);
	}

    public ArrayList<Order> findOrdersByCustomer (Customer customer) {
        return findOrdersByCustomer(customer, false);
    }
    
    public ArrayList<Order> findNotInvoicedOrdersByCustomer (Customer customer) {
        return findOrdersByCustomer(customer, true);
    }

    private ArrayList<Order> findOrdersByCustomer (Customer customer, boolean isNotInvoiced)
    {
        OrderInterface orderDb = new OrderDb();
        ArrayList<Order> orders = new ArrayList<Order>();
        try {
            String query = ""
                + "SELECT * "
                + "FROM Orders "
                + "JOIN Customer ON customer_id = order_customerid ";

            query += "WHERE customer_id = " + customer.getId();

            if(isNotInvoiced) {
                query += " AND order_id NOT IN (SELECT invoiceOrders_orderid FROM InvoiceOrders)";
            }

            ResultSet rs = this.db.query(query);

            if (rs != null) {
                while(rs.next()) {
                    Order order = orderDb.getOrder(rs.getInt("order_id"));

                    orders.add(order);
                }

            }
        } catch (SQLException ex) {
            Logger.getLogger(CustomerDB.class.getName()).log(Level.SEVERE, null, ex);
        }

        return orders;
    }


}
